﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using Npgsql;
//using SharpMap.Geometries;

using GeoAPI.Geometries;
using NetTopologySuite.IO;
using SpectationClient.Stuff;
using SpectationClient.DataBaseDescription;
using SpectationClient.SpectralTools;
using System.Data;

namespace SpectationClient.SQLCommandBuilder {
    static class CommandBuilder {
        private static WKBReader WKBReader = new WKBReader();


        public static NpgsqlCommand getSELECT(TableInfo ti) {
            return CommandBuilder.getSELECT(ti, false);
        }
        
        public static NpgsqlCommand getSELECT(TableInfo ti, bool hideBigBLOBs) {
            List<String> columns = ti.Keys.ToList<String>();
           
            bool fullselect = true;
          
            for(int i = 0; i < columns.Count; i++) {
               
                String column = columns[i];
                ColumnInfo ci = ti[column];
                /*
                if(!RecentTableInfo[column].Show) {
                    columns[i] = String.Format("'<not loaded>'::text as {0}", column);
                    fullselect = false;
                }*/
                if(ci is ColumnInfoGeometry) {
                    columns[i] = String.Format("ST_AsBinary(\"{0}\") AS \"{0}\"", column);
                    fullselect = false;
                }
                if(hideBigBLOBs) {
                    if(ci is ColumnInfoImageFile) {
                        ColumnInfoImageFile ciImg = ti[column] as ColumnInfoImageFile;
                        if(ciImg.mainImageFile == ciImg) {
                            columns[i] = String.Format("'<not loaded>'::text as {0}", column);
                            fullselect = false;
                        }
                    }
                
                }
            }
            NpgsqlCommand cmd = new NpgsqlCommand();
            if(fullselect){
                cmd.CommandText = String.Format("SELECT * FROM {0}", ti.SchemaTableName);
            }else{
                cmd.CommandText = String.Format("SELECT {0} FROM {1}", TextHelper.combine(columns, ","), ti.SchemaTableName);
            }
            return cmd;
        }

        private static List<String> getParameterNames(NpgsqlCommand command) {
            return (from x in command.Parameters
                    select x.ParameterName).ToList();
        }

     

        /// <summary>
        /// Returns a Npgsql Command to insert all values from a data table into an SQL relation.
        /// </summary>
        /// <param name="ti"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static NpgsqlCommand getINSERT(TableInfo ti, DataTable dt, bool AddedRowsOnly = false) {
            
            SpectralTools.ESL_Writer ESL_Writer = new SpectralTools.ESL_Writer();
            SpectralTools.ASD_Writer ASD_Writer = new SpectralTools.ASD_Writer();

            if(AddedRowsOnly) dt = dt.GetChanges(DataRowState.Added);

            if(dt == null || dt.Rows.Count == 0) return null;

            if(ti == null) ti = new TableInfo("empty");

            var specialColumns  = (from ColumnInfo c in ti.Values
                                  where dt.Columns.Contains(c.Name) && (
                                        c is ColumnInfoFile ||
                                        c is ColumnInfoGeometry ||
                                        c is ColumnInfoImageFile ||
                                        c is ColumnInfoSpectralFile)
                                  select c.Name).ToArray();

           
            InsertValueCollection ivc = new InsertValueCollection(ref ti);

            foreach(DataRow row in dt.Rows){
                InsertValues iv = new InsertValues(ref ti);

                foreach(DataColumn col in dt.Columns){
                    if(row[col.Ordinal] == null) continue;
                    Object value = row[col.Ordinal];
                    String cName = col.ColumnName;

                    if(specialColumns.Contains(col.ColumnName)){
                        if(ti[cName] is ColumnInfoGeometry){

                            Int32 srid = (ti[cName] as ColumnInfoGeometry).SRID;
                            if(value is String) {
                                iv.AddCoordinate(cName, value as String, srid);
                            } else 
                            if(value is Byte[]) {
                                iv.AddCoordinate(cName, value as Byte[], srid);
                            } else {
                                throw new NotImplementedException();
                            }

                        } else
                        if(ti[cName] is ColumnInfoImageFile ||
                           ti[cName] is ColumnInfoFile){

                            if(value is Byte[]){
                                iv.Add(cName, value as Byte[]);
                            }else{
                                throw new NotImplementedException();
                            }
                            
                        } else 
                        if(ti[cName] is ColumnInfoSpectralFile){
                            if(value is Byte[]){
                                iv.Add(cName, value as Byte[]);
                            }else
                            if(value is Spectrum) {
                                
                                  throw new NotImplementedException();

                            }
                        } else {
                        
                            throw new NotImplementedException();
                        }

                    }else{
                        iv.Add(cName, value);
                    }
                
                }

                ivc.Add(iv);

            }

            return ivc.getInsertCmd() ;
        }

        /// <summary>
        /// Create a DELETE command for a certain data table.
        /// </summary>
        /// <param name="ti"></param>
        /// <param name="dt"></param>
        /// <param name="DeletedRowsOnly"></param>
        /// <returns></returns>
        public static NpgsqlCommand getDELETE(TableInfo ti, DataTable dt, bool DeletedRowsOnly = false) {
            if(DeletedRowsOnly) dt = dt.GetChanges(DataRowState.Deleted);
            if(ti.Keys.Count == 0) throw new NotImplementedException();

            if(dt == null || dt.Rows.Count == 0) return null;
            NpgsqlCommand deleteCommand = null;

            List<Object[]> keyValueList = new List<object[]>();
           
            List<String> keyColumns = ti.PrimaryKey.Names;
            if(keyColumns.Count == 1) {

                Condition c = new Condition(keyColumns[0], Condition.Op.EQ);

                foreach(DataRow row in dt.Rows) {
                    c.Add(row[keyColumns[0]]);
                }

                deleteCommand = c.getCmd();
                

            } else {
                foreach(DataRow row in dt.Rows) {
                    Object[] keyValues = new Object[keyColumns.Count];
                    for(int i = 0; i < keyColumns.Count; i++) {
                        keyValues[i] = row[keyColumns[i]];
                    }
                    
                    keyValueList.Add(keyValues);
                }
                ConditionList cl = new ConditionList(ConditionList.Op.OR);
                cl.AddIDs(keyColumns, keyValueList);
                deleteCommand = cl.getCmd();
            }
            deleteCommand.CommandText = String.Format("DELETE FROM {0} WHERE {1}", ti.SchemaTableName, deleteCommand.CommandText);

            return deleteCommand;

        }

        /// <summary>
        /// Creates an UPDATE command for a single DataRow.
        /// </summary>
        /// <param name="ti"></param>
        /// <param name="iPoint"></param>
        /// <param name="prefixNumber"></param>
        /// <returns></returns>
        private static NpgsqlCommand getUPDATE(TableInfo ti, DataRow row, int prefixNumber) {
            
            NpgsqlCommand cmd = new NpgsqlCommand(String.Format("UPDATE {0} SET ", ti.SchemaTableName));
            List<String> subCmds = new List<String>();

            for(int i = 0; i < row.Table.Columns.Count; i++) {
                Object current = row[i, DataRowVersion.Current];
                Object original = row[i, DataRowVersion.Original];
                if(!Object.Equals(current, original)) {
                    
                    String key = String.Format("@{0}_{1}", prefixNumber, i);
                    String columnName = row.Table.Columns[i].ColumnName;
                    String sqlColumName = columnName;
                    
                    if(columnName != columnName.ToLower()) sqlColumName = "\""+columnName+"\"";

                    Object value = row[i, DataRowVersion.Current];
                    if(ti[columnName] is ColumnInfoGeometry) {
                        int srid = (ti[columnName] as ColumnInfoGeometry).SRID;
                        if(value is Byte[]) {
                            subCmds.Add(String.Format("{0} = ST_GeomFromWKB({1}, {2})", sqlColumName, key, srid));
                        
                        } else {
                            throw new NotImplementedException("unknown Geometry");
                        }

                    } else {
                        subCmds.Add(String.Format("{0} = {1}", sqlColumName, key));
                    }

                    cmd.Parameters.AddWithValue(key, value);
                    
                }
            }

            cmd.CommandText += TextHelper.combine(subCmds, ", ");

            List<String> keyColumns = ti.PrimaryKey.Names;
            
            ConditionList cl = new ConditionList(ConditionList.Op.OR);
            Object[] keyObjects = (from String columnName in keyColumns
                    select row[columnName, DataRowVersion.Original]).ToArray();
            List<Object[]> keyObjectList = new List<object[]>();
            keyObjectList.Add(keyObjects);
            cl.AddIDs(keyColumns, keyObjectList);

            cmd.CommandText += " WHERE ";
            CommandBuilder.appendCmd(ref cmd, cl.getCmd());
            
                    
            
            cmd.CommandText += ";\n";
            return cmd;
        }

        public static NpgsqlCommand getUPDATE(TableInfo ti, DataTable dt, bool modifiedRowsOnly) {
            if(modifiedRowsOnly) dt = dt.GetChanges(DataRowState.Modified);

            if(dt == null || dt.Rows.Count == 0) return null;
                 
            NpgsqlCommand cmd = new NpgsqlCommand();
            for(int iRow = 0; iRow < dt.Rows.Count; iRow++){
                CommandBuilder.appendCmd(ref cmd, getUPDATE(ti, dt.Rows[iRow], iRow));
            }
            return cmd;
        }

     


   
        public static NpgsqlCommand getCmd(String begin, NpgsqlCommand cmd, String end) {
            NpgsqlCommand cmd2 = new NpgsqlCommand();
            cmd2.CommandText += begin;
            CommandBuilder.appendCmd(ref cmd2, cmd);
            cmd2.CommandText += end;
            return cmd2;
        }

        /*
        public static void appendCmd(ref NpgsqlCommand target, ConditionList clTableOIDs, String prefix) {
            SQLCommandBuilder.appendCmd(ref target, clTableOIDs.getCmd(prefix));
        }
        public static void appendCmd(ref NpgsqlCommand target, Condition cDBColumnName, String prefix) {
            SQLCommandBuilder.appendCmd(ref target, cDBColumnName.getCmd(prefix));
        }*/

        public static void appendCmd(ref NpgsqlCommand target, AbstractCondition c) {
            CommandBuilder.appendCmd(ref target, c.getCmd());
        }
        public static void appendCmd(ref NpgsqlCommand target, AbstractCondition c, String prefix) {
            CommandBuilder.appendCmd(ref target, c.getCmd(prefix));
        }
    
        /// <summary>
        /// Appends an NpgsqlCommand, replaces parameter string placeholders
        /// </summary>
        /// <param name="target"></param>
        /// <param name="appendix"></param>
        public static void appendCmd(ref NpgsqlCommand target, NpgsqlCommand appendix) {
            //ensure distjoint command names
            if(target.CommandText.Length > 0 && !target.CommandText.EndsWith(" ")) {
                target.CommandText += " ";
            }
            NpgsqlCommand cmd2 = appendix;
            int offset = target.Parameters.Count;
            for(int i = 0; i < appendix.Parameters.Count; i++){
                String newParameterName = String.Format("@p{0}", offset+i);
                cmd2.CommandText = cmd2.CommandText.Replace(
                      cmd2.Parameters[i].ParameterName
                    , newParameterName);
                cmd2.Parameters[i].ParameterName = newParameterName;
            }
            target.CommandText += cmd2.CommandText;
            foreach(NpgsqlParameter p in cmd2.Parameters) target.Parameters.Add(p);

            /*
            foreach(NpgsqlParameter p in appendix.Parameters) {
                String newParameterName = p.ParameterName;
                int i = 0;
                while(target.Parameters.Contains(newParameterName)) {
                    newParameterName = String.Format("{0}{1}", p.ParameterName, ++i);
                }
                if(i > 0) {
                    cmd2.CommandText = cmd2.CommandText.Replace(p.ParameterName, newParameterName);
                    cmd2.Parameters[p.ParameterName].ParameterName = newParameterName;
                }
            }
            target.CommandText += cmd2.CommandText;

            foreach(NpgsqlParameter p in cmd2.Parameters) {
                target.Parameters.Add(p);
            }
            */
        }

     

        public static String CommandToString(List<NpgsqlCommand> list) {
            String s = "";
            foreach(NpgsqlCommand cmd in list) s += CommandBuilder.CommandToString(cmd, true);
            return s;
        }

        public static String CommandToString(NpgsqlCommand cmd) {
                return CommandBuilder.CommandToString(cmd, true);

        }
        public static String CommandToString(NpgsqlCommand cmd, bool addLineBreaks) {
            if(cmd == null) return "";
            NpgsqlCommand cmd2 = cmd.Clone();
        
            String str = cmd2.CommandText;
            ///TODO Replace Strings with Regex to match on full words

            foreach(NpgsqlParameter p in cmd.Parameters) {
                Regex r = new Regex(p.ParameterName + "(?=( |,|[)]|$))", RegexOptions.CultureInvariant);
                String pstring = "";
                if(p.NpgsqlDbType == NpgsqlTypes.NpgsqlDbType.Text ||
                   p.NpgsqlDbType == NpgsqlTypes.NpgsqlDbType.Char ||
                   p.NpgsqlDbType == NpgsqlTypes.NpgsqlDbType.Varchar) {
                    if(p.Value == null || p.Value == DBNull.Value) {
                        pstring = "null";
                    } else {
                        pstring = "'" + p.Value.ToString() + "'";
                    }
                } else if(p.NpgsqlDbType == NpgsqlTypes.NpgsqlDbType.Bytea){
                    Byte[] ba = (Byte[])p.Value;
                    //What kind of BLOB is it?
                    //is it a WKB-Blob
                    IGeometry g;
                    if(CommandBuilder.tryParse(ba, out g)) {
                        pstring = g.AsText();

                    } else {

                        //type of byte array unspecified
                        pstring = "<BLOB>";
                    }
                }else{
                    pstring = p.Value.ToString();
                }
                str = r.Replace(str, pstring);

            }
            //Format SQL Text
            if(str.Length > 0 && addLineBreaks) {
                str = str.Replace(" WHERE ", "\nWHERE ");
                str = str.Replace(" GROUP BY ", "\nGROUP BY ");
                str = str.Replace(" FROM ", "\nFROM ");
                str = str.Replace(" LIMIT ", "\nLIMIT ");
                str = str.Replace(" AND ", "\n\tAND ");
                str = str.Replace(" OR ", "\n\tOR ");
                str = str.Replace(" UNION ", "\n\tUNION ");
                str = str.Replace(" INTERSECT ", "\n\tINTERSECT ");
                str = str.Replace("(INSERT INTO ", "(\n\tINSERT INTO ");
                str = str.Replace(" VALUES ", "\nVALUES ");
                str = str.Replace("),(", ")\n,(");
            }
            String[] test = Regex.Split(str, "@(INSERT INTO)|(VALUES)|((SELECT *)|(SELECT))", RegexOptions.IgnoreCase);
            return str;
        }

        /// <summary>
        /// Returns a partial command that searches all rows that contain a word in one of the tables string-columns
        /// </summary>
        /// <param name="RecentTableInfo"></param>
        /// <param name="searchword"></param>
        /// <param name="casesensitive"></param>
        /// <param name="exactmatch"></param>
        /// <returns></returns>
        public static NpgsqlCommand getStringSearch(TableInfo ti, String searchword, bool matchExact, bool caseSensitive) {
            ConditionList cl = new ConditionList(ConditionList.Op.OR);
            Condition.Op cop = Condition.getStringComparer(matchExact, caseSensitive);
            foreach(ColumnInfo ci in ti.Values) {
                if(ci.ValueType == typeof(String)) {
                    cl.Add(new Condition(ci.Name, cop, searchword));
                }
            }
            return cl.getCmd();
        }

        private static bool tryParse(Byte[] ba, out IGeometry geom) {
            bool result = false;
            try {
                geom  = WKBReader.Read(ba);
                result = true;
            } catch {
                geom = null;
            }
           
            return result;
        }


    }
}
